﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entities;
using DataAccess;
using MySql.Data.MySqlClient;
using System.Data;
namespace BusinessFacade
{
    public class Busi_nm_noteplan
    {
        public Busi_nm_noteplan()
        {
        }

        /// <summary>
        ///  增加一条数据
        /// </summary>
        public bool Add(nm_noteplan_Model model, su_mapped_Model model1)
        {
            int rowsAffected;

            MySqlParameter[] parameters = {

                    new MySqlParameter("pPlanId", MySqlDbType.Int32,11),

                    new MySqlParameter("pmapID", MySqlDbType.Int32,10),

					new MySqlParameter("pplanname", MySqlDbType.VarChar,150),

                    new MySqlParameter("pmoubleID",MySqlDbType.Int32,10),

                    new MySqlParameter("pplanexplain", MySqlDbType.VarChar,200),

                    new MySqlParameter("pnotecontent", MySqlDbType.VarChar,200),

                    new MySqlParameter("psendtime", MySqlDbType.DateTime),

                    new MySqlParameter("pAuditingPerson" ,MySqlDbType.VarChar,200),

                    new MySqlParameter("pCreatePersonID",MySqlDbType.Int32 ,10),

                    new MySqlParameter("pCreateTime",MySqlDbType.Date),

                    new MySqlParameter("pAuditingState", MySqlDbType.Int32,10),
                  
                    new MySqlParameter("pAuditingTime",MySqlDbType.Date),

                    new MySqlParameter("pAuditingSuggestion",MySqlDbType.Text),

                    new MySqlParameter ("pPersonGroup", MySqlDbType.VarChar,200),

                    new MySqlParameter("PName", MySqlDbType.VarChar,150),

                    new MySqlParameter("PMappedChar", MySqlDbType.VarChar,150),

                  	new MySqlParameter("pTelPhone", MySqlDbType.VarChar,11),
                                          };
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Direction = ParameterDirection.Output;
            parameters[2].Value = model.PlanName;
            parameters[3].Value = model.moubleID;
            parameters[4].Value = model.PlanExplain;
            parameters[5].Value = model.NoteContent;
            parameters[6].Value = model.SendTime.ToString("yyyy-MM-dd");
            parameters[7].Value = model.AuditingPerson;
            parameters[8].Value = model.CreatePersonID;
            parameters[9].Value = model.CreateTime.ToString("yyyy-MM-dd"); ;
            parameters[10].Value = model.AuditingState;
            parameters[11].Value = model.AuditingTime.ToString("yyyy-MM-dd");
            parameters[12].Value = model.AuditingSuggestion;
            parameters[13].Value = model.PersonGroup;
            parameters[14].Value = model1.Name;
            parameters[15].Value = model1.MappedChar;
            parameters[16].Value = model1.TelPhone;

            DbHelperMySql.RunProcedure("nm_noteplan_ADD", parameters);
            rowsAffected = int.Parse(parameters[0].Value.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 插入映射表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool su_mapped_ADD(su_mapped_Model model)
        {
            int rowsAffected;
            MySqlParameter[] parameters = {

                    new MySqlParameter("pmapID", MySqlDbType.Int32),

					new MySqlParameter("pName", MySqlDbType.VarChar,100),

					new MySqlParameter("pMappedChar", MySqlDbType.VarChar,500),

					new MySqlParameter("pTelPhone", MySqlDbType.VarChar,11),

                    new MySqlParameter("pPlanID",MySqlDbType.Int32)
                 
                                          };
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.MappedChar;
            parameters[3].Value = model.TelPhone;
            parameters[4].Value = model.PlanID;
            DbHelperMySql.RunProcedure("su_mapped_ADD", parameters);
            rowsAffected = int.Parse(parameters[0].Value.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            return false;
        }



        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        public void Update(nm_noteplan_Model model)
        {
            MySqlParameter[] parameters = {
                   
                    new MySqlParameter("pPlanId",MySqlDbType.Int32),

                    new MySqlParameter("pplanname", MySqlDbType.VarChar,150),

					new MySqlParameter("pplanmouble", MySqlDbType.VarChar,150),

					new MySqlParameter("pplanexplain", MySqlDbType.VarChar,254),

					new MySqlParameter("pnotecontent", MySqlDbType.VarChar,254),
                    
                    new MySqlParameter("psendtime",MySqlDbType.DateTime),
                                          };
            parameters[0].Value = model.PlanId;
            parameters[1].Value = model.PlanName;
            parameters[2].Value = model.moubleID;
            parameters[3].Value = model.PlanExplain;
            parameters[4].Value = model.NoteContent;
            parameters[5].Value = model.SendTime;
            DbHelperMySql.RunProcedure("nm_noteplan_Update", parameters);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="PlanName"></param>
        /// <param name="MoubleID"></param>
        /// <returns></returns>
        public DataTable GetList(string PlanName, int MoubleID)
        {
            DataSet ds = new DataSet();
            MySqlParameter[] parameters = {
                    new MySqlParameter("planname", MySqlDbType.VarChar,50),
					new MySqlParameter("pmoubleID", MySqlDbType.Int32,5)};
            parameters[0].Value = PlanName;
            parameters[1].Value = MoubleID;
            ds = DbHelperMySql.RunProcedure("nm_noteplan_Select", parameters);
            return ds.Tables[0];
        }

        /// <summary>
        /// 得到值
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public nm_noteplan_Model GetListModel(int id)
        {
            nm_noteplan_Model model = new nm_noteplan_Model();
            string sql = "select * from nm_noteplan where planid=" + id;
            DataSet ds = DbHelperMySql.Query(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["planid"] != null && ds.Tables[0].Rows[0]["planid"].ToString() != "")
                {
                    model.PlanId = int.Parse(ds.Tables[0].Rows[0]["planid"].ToString());
                }
                if (ds.Tables[0].Rows[0]["planname"] != null && ds.Tables[0].Rows[0]["planname"].ToString() != "")
                {
                    model.PlanName = ds.Tables[0].Rows[0]["planname"].ToString();
                }
                if (ds.Tables[0].Rows[0]["moubleID"] != null && ds.Tables[0].Rows[0]["moubleID"].ToString() != "")
                {
                    model.moubleID = Convert.ToInt32((ds.Tables[0].Rows[0]["moubleID"]).ToString());
                }

                if (ds.Tables[0].Rows[0]["notecontent"] != null && ds.Tables[0].Rows[0]["notecontent"].ToString() != "")
                {
                    model.NoteContent = ds.Tables[0].Rows[0]["notecontent"].ToString();
                }


                if (ds.Tables[0].Rows[0]["planexplain"] != null && ds.Tables[0].Rows[0]["planexplain"].ToString() != "")
                {
                    model.PlanExplain = ds.Tables[0].Rows[0]["planexplain"].ToString();
                }

                if (ds.Tables[0].Rows[0]["sendtime"] != null && ds.Tables[0].Rows[0]["sendtime"].ToString() != "")
                {
                    model.SendTime = Convert.ToDateTime(ds.Tables[0].Rows[0]["sendtime"].ToString());
                }

                return model;
            }
            model = null;
            return model;
        }


        /// <summary>
        /// 获取最大值
        /// </summary>
        /// <returns></returns>
        public DataSet GetMaxValue() 
        {
            string SQL = "SELECT max(planid)as NUM FROM nm_noteplan";
            DataSet ds = DbHelperMySql.Query(SQL);
            return ds;
        }


        /// <summary>
        /// 总消费
        /// </summary>
        /// <param name="PlanId"></param>
        /// <returns></returns>
        public DataSet GetConsume(int safeID)
        {
            string SQL = "select paymentbase,paymentterm,arrearageterm,Consume from pm_staffmanage where  safeID='" + safeID + "'";
            DataSet ds = DbHelperMySql.Query(SQL);
            return ds;
        }


        /// <summary>
        /// 总收入
        /// </summary>
        /// <param name="PlanId"></param>
        /// <returns></returns>
        public DataSet GeTincome(int SafeID)
        {
            string SQL = "select paymentbase,paymentterm,arrearageterm,Consume from pm_staffmanage where  safeID='" + SafeID + "'";
            DataSet ds = DbHelperMySql.Query(SQL);
            return ds;
        }

        public DataSet GetsafeID(int SafeID)
        {
            string SQL = "select Name from su_mapped where PlanId='" + SafeID + "'";
            DataSet ds = DbHelperMySql.Query(SQL);
            return ds;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        public static void Delete(int id)
        {
            MySqlParameter[] parameters = { 
                             new MySqlParameter("pPlanId",MySqlDbType.Int32)
                                         };
            parameters[0].Value = id;
            DbHelperMySql.RunProcedure("nm_noteplan_Delete", parameters);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="pName"></param>
        /// <param name="pPlanID"></param>
        public static void Delete_su_mapped(string pName, int pPlanID)
        {
            MySqlParameter[] parameters = { 
                             new MySqlParameter("pName",MySqlDbType.VarChar,100),
                                 new MySqlParameter("pPlanID",MySqlDbType.Int32)
                                         };
            parameters[0].Value = pName;
            parameters[1].Value = pPlanID;
            DbHelperMySql.RunProcedure("su_mapped_Delete", parameters);
        }
    }
}
